package es.mllacer.qqsm.util;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class LocalScoresDBHelper extends SQLiteOpenHelper {

	private static LocalScoresDBHelper helper = null;

	private static final String tableName = "LocalScores";

	public static LocalScoresDBHelper getInstance(Context context) {
		if (helper == null) {
			helper = new LocalScoresDBHelper(context);
		}
		return helper;
	}

	private LocalScoresDBHelper(Context context) {
		super(context, "qqsm.db", null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "CREATE TABLE " + tableName + " ("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "name TEXT NOT NULL, " + "longitude NUMERIC, "
				+ "latitude NUMERIC, " + "address TEXT, "
				+ "score INTEGER NOT NULL)";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// nothing to do
	}

	public HighScoreList getLocalScores() {
		String[] columns = { "name", "longitude", "latitude", "address",
				"score" };
		Cursor cursor = getReadableDatabase().query(tableName, columns, null,
				null, null, null, "score");

		int nameId = cursor.getColumnIndex("name");
		int longitudeId = cursor.getColumnIndex("longitude");
		int latitudeId = cursor.getColumnIndex("latitude");
		int addressId = cursor.getColumnIndex("address");
		int scoreId = cursor.getColumnIndex("score");

		List<HighScore> scores = new ArrayList<HighScore>();

		while (cursor.moveToNext()) {
			String name = cursor.getString(nameId);
			double longitude = cursor.getDouble(longitudeId);
			double latitude = cursor.getDouble(latitudeId);
			String address = cursor.getString(addressId);
			int scoring = cursor.getInt(scoreId);

			scores.add(new HighScoreWithAddress(name, longitude, latitude,
					scoring, address));
		}

		HighScoreList list = new HighScoreList();
		list.setScores(scores);

		return list;
	}

	public void deleteLocalScores() {
		getWritableDatabase().delete(tableName, "1", null);
	}

	public void insertLocalScore(String name, float longitude, float latitude,
			String address, int score) {
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("longitude", longitude);
		cv.put("latitude", latitude);
		cv.put("address", address);
		cv.put("score", score);

		getWritableDatabase().insert(tableName, null, cv);
	}

}
